--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库，名称为Student
use master;
go
-- 检测原库是否存在，如果存在则删除
if exists (select * from sys.databases where name = 'Student')
	drop database Student;
go
-- 创建一个库
create database Student;
go
-- 使用这个库
use Student;
go


--------------------
-- 建表部分
--------------------
-- 创建班级表，存储班级信息，其中字段包含：班级id、班级名称
create table Class(
	ClassId int not null identity(60,2),
	ClassName nvarchar(50) not null
);
go

-- 创建学生表，存储学生信息，其中字段保护：学生id、姓名、性别、生日、家庭住址，所属班级id
create table Student (
	StudentId int not null identity(20, 3),
	StudentName nvarchar(50),
	StudentSex tinyint not null,
	StudentBirth date,
	StudentAddress nvarchar(255) not null
);
go

-- 创建课程表，存储课程信息，其中字段包含：课程id、课程名称、课程学分
create table Course(
	CourseId int identity(3,2),
	CourseName nvarchar(50),
	CourseCredit int
);
go

-- 创建班级课程表，存储班级课程信息，其中字段包含：自增id、班级id、课程id
create table ClassCourse(
	ClassCourseId int identity(10,1),
	ClassId int,
	CourseId int
);
go

-- 创建分数表，存储学生每个课程分数信息，其中字段包含：分数id、学生id、课程id、分数
create table Score(
	ScoreId int identity(5,1),
	StudentId int,
	CourseId int,
	Score int
);
go


-- 学生表建好了，细想一下少了一个所属班级的字段
-- 给学生表 Student 增加一个所属班级id字段 
alter table Student add ClassId int not null;
go

----------------------------------------
-- 创建约束部分，使用alter进行修改
----------------------------------------
-- 班级表 ClassId 字段需要设置为主键（主键约束）
alter table Class add constraint PK_Class_ClassId primary key (ClassId);
-- 学生表 StudentId 字段需要设置为主键（主键约束）
alter table Student add constraint PK_Student_StudentId primary key (StudentId);
-- 课程表 CourseId 字段需要设置为主键（主键约束）
alter table Course add constraint PK_Course_CourseId primary key (CourseId);
-- 班级课程表 ClassCourseId 字段需要设置为主键（主键约束）
alter table ClassCourse add constraint PK_ClassCourse_ClassCourseId primary key (ClassCourseId);
-- 分数表 ScoreId 字段需要设置为主键（主键约束）
alter table Score add constraint PK_Score_ScoreId primary key (ScoreId);

-- 学生表 StudentName 不允许为空（非空约束）
alter table Student alter column StudentName nvarchar(50) not null;

-- 班级表 ClassName 需要唯一（唯一约束）
alter table Class add constraint UQ_Class_ClassName unique(ClassName);
-- 课程表 CourseName 需要唯一（唯一约束）
alter table Course add constraint UQ_Course_CourseName unique(CourseName);

-- 学生表 ClassId 增加默认值为0（默认值约束）
alter table Student add constraint DF_Student_ClassId default(0) for ClassId;

-- 学生表 StudentSex 只能为1或者2（Check约束）
alter table Student add constraint CK_Student_StudentSex check(StudentSex=1 or StudentSex=2 or StudentSex=3);
-- 分数表 Score 字段只能大于等于0（check约束）
alter table Score add constraint CK_Score_Score check(Score>=0);
-- 课程表 CourseCredit 字段只能大于0（check约束）
alter table Course add constraint CK_Course_CourseCredit check(CourseCredit>=0);

-- 班级课程表ClassId 对应是 班级表ClassId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_ClassId foreign key (ClassId) references Class(ClassId);
-- 班级课程表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_CourseId foreign key (CourseId) references Course(CourseId);
-- 分数表StudentId 对应是 学生表StudentId 的外键 （外键约束）
alter table Score add constraint FK_Score_StudentId foreign key (StudentId) references Student(StudentId);
-- 分数表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table Score add constraint FK_Score_CourseId foreign key (CourseId) references Course(CourseId);


insert into Class(ClassName) values ('软件一班'),('软件二班'),('计算机应用技术班');

insert into Student (StudentName,StudentSex,StudentBirth,StudentAddress,ClassId)
	values ('刘正','1','2000-01-01','广西省桂林市七星区空明西路10号鸾东小区','60')
	,('黄贵','1','2001-03-20','广西省桂林市七星区空明西路10号鸾东小区','60')
	,('陈美','2','2000-07-08','福建省龙岩市新罗区曹溪街道万达小区','60');

insert into Student (StudentName,StudentSex,StudentBirth,StudentAddress,ClassId)
	values ('江文 ','1','2000-08-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光','62')
	,('钟琪','2','2001-03-21','湖南省长沙市雨花区红花坡社区','62');

insert into Student (StudentName,StudentSex,StudentBirth,StudentAddress,ClassId)
	values ('曾小林','1','1999-12-10','徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光','64')
	,('欧阳天天','2','2000-04-05','湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府','64')
	,('徐长卿','1','2001-01-30','江苏省苏州市苏州工业园区独墅湖社区','64')
	,('李逍遥','1','1999-11-11','广东省广州市白云区金沙洲岛御洲三街恒大绿洲','64');

insert into Student (StudentName,StudentSex,StudentBirth,StudentAddress)
	values ('东方不败','3','1999-12-11','河北省平定州西北四十余里的猩猩滩')
	,('令狐冲','1','2000-08-11','陕西省渭南市华阴市玉泉路南段');

insert into Course (CourseName,CourseCredit)
	values ('数据库高级应用','3')
	,('javascript编程基础','3')
	,('web前端程序设计基础','4')
	,('动态网页设计.net基础','6')
	,('动态网页设计php基础','6')

insert into ClassCourse (ClassId,CourseId)
	values ('60','3')
	,('60','5')
	,('60','7')
	,('60','9')

insert into ClassCourse (ClassId,CourseId)
values ('62','3')
,('62','5')
,('62','7')
,('62','9')

insert into ClassCourse (ClassId,CourseId)
values ('64','3')
,('64','5')
,('64','7')
,('64','11')

insert into Score (StudentId,CourseId,Score) 
	values ('20','3','80')
		,('20','5','78')
		,('20','7','65')
		,('20','9','90')
		,('23','3','60')
		,('23','5','77')
		,('23','7','68')
		,('23','9','88')
		,('26','3','88')
		,('26','5','45')
		,('26','7','66')
		,('26','9','75')
		,('29','3','56')
		,('29','5','80')
		,('29','7','75')
		,('29','9','66')
		,('32','3','88')
		,('32','5','79')
		,('32','7','72')
		,('32','9','85')
		,('35','3','68')
		,('35','5','88')
		,('35','7','73')
		,('35','11','63')
		,('38','3','84')
		,('38','5','90')
		,('38','7','92')
		,('38','11','78')
		,('41','3','58')
		,('41','5','59')
		,('41','7','65')
		,('41','11','75')
		,('44','3','48')
		,('44','5','67')
		,('44','7','71')
		,('44','11','56')
		,('44','3','48');


--7. 显示班级信息，假设每页3条记录，请查询出第2页的数据，按照班级id升序（分页查询）。
select * from Class order by ClassId asc offset 3 rows fetch next 3 rows only;

--8. 显示学生信息，假设每页5条记录，请查询出第1页的数据，按照学号升序（分页查询）。
select * from Student order by StudentId asc offset 0 rows fetch next 5 rows only;

--9. 显示学生信息，假设每页5条记录，请查询出第2页的数据，按照学号升序（分页查询）。
select * from Student order by StudentId asc offset 5 rows fetch next 5 rows only;

--10. 显示成绩信息，假设每页显示8条记录，请查询出第3页的数据，按照分数降序排列（分页查询）。
select * from Score order by Score desc offset 16 rows fetch next 8 rows only;


/*11. 查询学生姓名，显示学号、姓名、性别、家庭住址，其中性别显示：（case when）。
	1. 1：男
	2. 2：女
	3. 3：保密*/
select 
	StudentId as 学号,
	StudentName as 姓名,
	case 
		when StudentSex =1 then '男'
		when StudentSex =2 then '女'
		when StudentSex =3 then '保密'
		else '未知'
		end as 性别,
	StudentAddress as 家庭住址
from Student;


/*12. 查询学生成绩，显示学生id、课程id、分数、评级，其中评级如下：（case when）。
	1. >=90：优秀
	2. 80-89：良好
	3. 70-79：中等
	4. 60-69：及格
	5. 0-59：不及格*/
select
	StudentId as 学生id,
	CourseId as 课程id,
	Score as 分数,
	case	
		when Score >=90 then '优秀'
		when Score>79 and Score <90 then '良好'
		when Score>69 and Score <80 then '中等'
		when Score>59 and Score <70 then '及格'
		when Score>=0 and Score <60 then '不及格'
		else '未知'
		end as '评级'
from Score


--13. 查询每门课程上课的班级总数，显示：课程id、课程名称、课程学分、班级数（连接查询 + 聚合查询 + 分组）。
select * from ClassCourse
select * from Course
select Course.CourseId,Course.CourseName,Course.CourseCredit,count(0) as 班级数 from ClassCourse
	inner join Course on ClassCourse.CourseId = Course.CourseId
	group by Course.CourseId,Course.CourseName,Course.CourseCredit
	order by  班级数;

--14. 查询每门课程上课的学生总数，显示：课程id、课程名称、课程学分、学生人数（连接查询 + 聚合查询 + 分组）。
select * from ClassCourse
select * from Course
select * from Student
select Course.CourseId,Course.CourseName,Course.CourseCredit,count(0) as 学生人数 from ClassCourse
	inner join Course on ClassCourse.CourseId = Course.CourseId
	inner join Student on ClassCourse.ClassId = Student.ClassId
	group by Course.CourseId,Course.CourseName,Course.CourseCredit 
	order by 学生人数;

/*15. 查询每个学生的平均分，并按照平均分降序排列。（连接查询 + 聚合查询 + 分组 + 分页 + 排序 ）。
	1. 显示：姓名、性别、平均分、班级名称，其中性别显示男、女或者保密。
	2. 每页显示3条，显示第2页记录*/

select 
Student.StudentName as 姓名,
		case 
		when StudentSex = 1 then '男'
		when StudentSex = 2 then '女'
		when StudentSex = 3 then '保密'
		else '未知'
		end as 性别,
	avg(Score) as 平均分,Class.ClassName as 班级名称 from Score	
	inner join Student on Score.StudentId = Student.StudentId
	inner join Class on Student.ClassId = Class.ClassId
	group by Student.StudentName,Student.StudentSex,Class.ClassName
	order by 平均分 desc offset 3 rows fetch next 3 rows only;
